***************************************************************************
* .do-file to combine all years of the SCF (waves 1989-2016)
***************************************************************************

clear all
set maxvar 6000

///////////////////////////////////////////////////////////
//////////////////////// wave 2016 ////////////////////////
///////////////////////////////////////////////////////////


use "$scfrawdata/2016/p16i6.dta", clear


// HH characteristics
ren X101  hh_num        // number of people in HH
ren X7001 peu_num		// number of people in PEU
ren X8000 rev			// orginal spouse head variables reversed

// weights
ren X42001 wgt_rv

// demographic variables
ren X8021 sexref    // sex of reference person
ren X103  sexsp     // sex of spouse

ren X5908 byref     // year of birth reference person
ren X6108 bysp		// year of birth spouse

ren X8022 ageref	// age reference person
ren X104  agesp     // age spouse

ren X8023 martref1  // marital status reference person (1. married, 2. living with partner, 3. separated, 4. divorced, 5. widowed, 6. never married )
ren X105  martsp1   // marital status spouse (1. married, 2. living with partner, 3. separated, 4. divorced, 5. widowed, 6. never married )

ren X5931 educref   // highest level of education, reference person
ren X6111 educsp    // highest level of education, spouse 

ren X7372 martref   // legal marital status, reference person (1. married, 3. separated, 4. divorced, 5. widowed, 6. never married)
ren X7018 mart2sp   // legal marital status, spouse (1. married, 3. separated, 4. divorced, 5. widowed, 6. never married)

ren X7402 indus_ref  // industry: reference person
ren X7412 indus_sp   // industry: spouse
ren X7401 occup_ref  // occupation: reference person
ren X7411 occup_sp   // occupation: spouse

// inheritances and gifts
ren X5801 inher_d       // Have you or your spouse been giving substatial gifts or inheritances? (reference person)

ren X5803 inher1        // Was the first transfer an inhertiance, trust, gift?
ren X5808 inher2        // Was the second transfer an inhertiance, trust, gift?
ren X5813 inher3        // Was the third transfer an inhertiance, trust, gift?

ren X5804 inher1_val    // Approximate value at the time received, first transfer 
ren X5809 inher2_val    // Approximate value at the time received, second transfer
ren X5814 inher3_val    // Approximate value at the time received, third transfer

ren X5805 inher1_year    // Year received, first transfer 
ren X5810 inher2_year    // Year received, second transfer
ren X5815 inher3_year    // Year received, third transfer

ren X5818 inheroth_val  // Amount of other transfers received 

ren X5819 inher_exp     // Do you or your partner expect to receive substantial gift or inheritance in the future?
ren X5821 inher_expamt  // About how much do you expect?

ren X5824 inher_imp     // Do you consider leaving an inhertitance as important?
ren X5825 inher_expown  // Do you or your spouse expect to leave a sizable estate?


// no of children and grandchildren in HH, no age restriction (i.e. also adult chilren in HH counted as children)
gen numkids = 0
foreach x in 102 108 114 120 126 132 202 208 214 220 226 {
replace numkids = numkids + 1 if X`x'== 4 | X`x' == 5 
}

// to number of children in household, add children living elsewhere
replace X5910 = 0 if X5910 == -1
replace numkids = numkids + X5910

// no of children in HH  + children living elsewhere below 25
replace X5911 = 0 if X5911 == -1
gen numkids25 = numkids - X5911 if X5911 != -2 & X5911 != .
replace numkids25 = numkids - X5910 if X5911 == -2

// total family income
ren X5702 finc_wage        // annual income from wages and salaries 
ren X5704 finc_business    // annual income from farm or proprietorships 
ren X5706 finc_ntaxinv     // annual income from non-taxable investments
ren X5708 finc_interest    // annual income from interest
ren X5710 finc_dividend    // annual income from dividends
ren X5712 finc_stocks      // annual income from sales of stocks or mutual funds
ren X5714 finc_otherbusi   // annual income from other business or investments
ren X5716 finc_compen      // annual income from unemployment or worker's compensation
ren X5718 finc_childsup    // annual income from child support or alimony payments
ren X5722 finc_socials     // annual income from social security (pensions, annuities, etc.)
ren X5720 finc_welfare     // annual income from welfare (TANF, SNAP, etc.)
ren X5724 finc_other       // annual income from other sources

// recode of variables
foreach x of varlist finc_business finc_otherbusi finc_stocks finc_other {
    replace `x' = 0 if `x' == -9 | `x' == -2 | `x' == -1 
}

// add withdrawls from IRAs to other income (included in other income before 2004)
* withdrawls from reference person
replace finc_other=finc_other+X6558+X6566+X6574 
* withdrawles from spouse and future benefits
foreach x in X6464 X6469 X6474 X6479 X6965 X6971 X6977 X6983 {
replace finc_other= finc_other + `x' if `x' >=0
}

// generate income measures
gen totinc_nocap = finc_wage + finc_compen + finc_childsup + finc_socials + ///
				   finc_welfare + finc_other
				   
gen totinc_nocapoth = finc_wage + finc_compen + finc_childsup + finc_socials + ///
				   finc_welfare 

// non-financial assets: value of vehicles
* personally owned cars (first four)
gen vehic = X8166 + X8167 + X8168 + X8188 
* personally owned cars (all others combined)
replace vehic = vehic +  X2422 
* other owned vehicles (motor home, RV, motorcycle, boat, airplane)
replace vehic = vehic + X2506 +X2606 + X2623


// non-financial assets: primary residence (excluding mortgages)
ren X513 hval_farm1 // land value of farm (if respondent owns all)
ren X526 hval_farm2 // land value of farm (if respondent owns part, refers to part R owns)

ren X507 farm_frac  // fraction of property used for farming
replace farm_frac = 0 if farm_frac < 0

ren X604 hval_mh1   // value of mobile home (only own site)
ren X614 hval_mh2   // value of mobile home (only own home)
ren X623 hval_mh3   // value of mobile home (own both)
ren X716 hval_house // value of home or apartment
replace hval_house = 0 if hval_house < 0	  
	
// home ownership variables
ren X501 hometype          // type of principle residence 
ren X508 owntype_farm      // ownership status of farm     
ren X601 owntype_mob       // ownership status of mobile home
ren X701 owntype_oth       // ownership status of other type of housing (house, apartment etc.)

// non-financial assets: further compenents

// land contracts and notes given by respondent
gen land_contracts = max(X1306,X1310) +max(X1325,X1329) +X1339 

// first and second property other than primary residence
gen oth_prop1 = X1706*(X1705/10000) // multiply with percentage owned 
gen oth_prop2 = X1806*(X1805/10000)

// remaining vacation properties
gen vac_prop = X2002

// all remaining properties other than vacation properties
replace X2012 = 0 if X2012 < 0
gen oth_prop3 = X2012


// debt: home equity lines of credit (first, second, and third line)
gen heloc = 0
replace heloc = heloc + X1108 if X1103 == 1
replace heloc = heloc + X1119 if X1114 == 1
replace heloc = heloc + X1130 if X1125 == 1

*fourth home equity line of credit: assume same fraction secured by home equity as for first three (above)
replace heloc = heloc + max(0,X1136)*(heloc/(X1108+X1119+X1130)) if heloc > 0
*fourth home equity line of credit: if not information on firt three, assume 50% secured by home equity
replace heloc = heloc + max(0,X1136)*0.5 if heloc == 0

// mortgages/ land contract/ equity loans on principle residence
gen nh_mort = X805+X905+X1005
gen mrthel = nh_mort + heloc

// debt: other residential debt (not secured by primary residence)
gen resdbt =X1318+X1337+X1342+X2006 + X1715*(X1705/10000) + X1815*(X1805/10000)+X2016

// debt: Credit Card Balances 
foreach var of varlist X413 X421 X427 {
replace `var' = 0 if `var' < 0	
}
gen ccbal = X413 + X421 + X427 + X7575

// vechicle loans: personally owned cars and other owned vehicles
gen veh_loan  = X2218 + X2318 + X2418 + X7169 + X2424 + X2519 + X2619 + X2625

// education loans: first six, and then all remaining together 
gen educ_loan = X7824 + X7847 + X7870 + X7924 + X7947 + X7970 + X7179 

// add consumer loans into education loans if purpose was education
replace educ_loan = educ_loan + X2723 if X2710 == 83
replace educ_loan = educ_loan + X2740 if X2727 == 83
replace educ_loan = educ_loan + X2823 if X2810 == 83
replace educ_loan = educ_loan + X2840 if X2827 == 83
replace educ_loan = educ_loan + X2923 if X2910 == 83
replace educ_loan = educ_loan + X2940 if X2927 == 83

// other installment loadns 
gen oth_install =  X7183 + X1044 + X1215 + X1219

// add consumer loans if purpose is other
replace oth_install = oth_install + X2723 if X2710 == 78
replace oth_install = oth_install + X2740 if X2727 == 78
replace oth_install = oth_install + X2823 if X2810 == 78
replace oth_install = oth_install + X2840 if X2827 == 78
replace oth_install = oth_install + X2923 if X2910 == 78
replace oth_install = oth_install + X2940 if X2927 == 78

// add to overall variable "installment loans"
gen inst_loan = veh_loan + educ_loan + oth_install 

// debt: other lines of credit ( lines of credit that are not secured by equity of home)
gen othloc = 0 
replace othloc = othloc + X1108 if X1103 != 1
replace othloc = othloc + X1119 if X1114 != 1
replace othloc = othloc + X1130 if X1125 != 1

*fourth line of credit: assume same fraction not secured by home equity as for first three (above)
replace othloc = max(0,X1136)*(othloc/(X1108+X1119+X1130)) if othloc > 0 
*fourth line of credit: if not information on firt three credti lines, assume 50% not secured by home equity
replace othloc = max(0,X1136)*0.5   if othloc == 0

// debt: other debt
gen odebt = 0
replace odebt = odebt + max(0,X11027) if X11070==5 // if not recorded earlier interview
replace odebt = odebt + max(0,X11127) if X11170==5
replace odebt = odebt + max(0,X11327) if X11370==5
replace odebt = odebt + max(0,X11427) if X11470==5

// add: borrowed on life insurance, miscellanous debts, margin loans
replace odebt = odebt + max(0,X4010) + max(0,X4032) + max(0,X3932)


///// FINANCIAL ASSETS /////

// checking accounts
foreach var of varlist X3506 X3510 X3514 X3518 X3522 X3526 X3529 {	
replace `var' = 0 if `var' < 0 
}
gen checking = X3506 + X3510 + X3514 + X3518 + X3522 + X3526 + X3529

// prepaid debit card or prepaid government benefit card
rename X7596 prepaid 
replace prepaid = 0 if prepaid < 0


// IRA/KEOGH accounts (roth IRAs, roll-over IRAs, regular IRAs, Keogh)
foreach var of varlist X6551 X6559 X6567 X6552 X6560 X6568 X6553 ///
		    X6561 X6569 X6554 X6562 X6570 {
replace `var' = 0 if `var' < 0
}			
gen IRAKH = X6551 + X6559 + X6567 + X6552 + X6560 + X6568 + X6553 + ///
		    X6561 + X6569 + X6554 + X6562 + X6570

// accounts referring to reference person			
gen IRAKH_ref = X6551 + X6552 + X6553 + X6554
// accounts referring to spouse
gen IRAKH_sp = X6559 + X6560 + X6561 + X6562
// accounts referring to other familiy members 
gen IRAKH_oth = X6567 + X6568 + X6569 + X6570

// percent of accounts invested in stocks
foreach var of varlist X6556 X6564 X6572 {
replace `var' = 0 if `var' < 0
}			
ren X6556 IRAKH_ref_ps
ren X6564 IRAKH_sp_ps
ren X6572 IRAKH_oth_ps

// how is the money in accounts invested (all in stocks, all in bonds etc.)
ren X6555 IRAKH_ref_invest
ren X6563 IRAKH_sp_invest
ren X6571 IRAKH_oth_invest

// certificates of deposits (cds)
ren X3721 cds

// savings + money market accounts 
foreach var of varlist X3730 X3736 X3742 X3748 X3754 X3760 X7075 X7078 ///
 X7081 X7084 X7087 X7090 X3765 {
replace `var' = 0 if `var'< 0
}


// savings account refers to: traditional savings account, coverdell/education IRA/KEOGH
// savings account for designated purpose, share accoutn, informal group savings arrangement
// other, and foreign account type

gen savings = 0
replace savings = X3730 if X3732 == 1 | X3732 == 2 | X3732 == 5 | ///
	X3732 == 6 | X3732 == 14 | X3732 == 0 | X3732 == -7 | X3732 == 20	
replace savings = savings + X3736 if X3738 == 1 | X3738 == 2 | X3738 == 5 | ///
	X3738 == 6 | X3738 == 14 | X3738 == 0 | X3738 == -7 | X3738 == 20	
replace savings = savings + X3742 if X3744 == 1 | X3744 == 2 | X3744 == 5 | ///
	X3744 == 6 | X3744 == 14 | X3744 == 0 | X3744 == -7 | X3744 == 20	
replace savings = savings + X3748 if X3750 == 1 | X3750 == 2 | X3750 == 5 | ///
	X3750 == 6 | X3750 == 14 | X3750 == 0 | X3750 == -7 | X3750 == 20	
replace savings = savings + X3754 if X3756 == 1 | X3756 == 2 | X3756 == 5 | ///
	X3756 == 6 | X3756 == 14 | X3756 == 0 | X3756 == -7 | X3756 == 20	
replace savings = savings + X3760 if X3762 == 1 | X3762 == 2 | X3762 == 5 | ///
	X3762 == 6 | X3762 == 14 | X3762 == 0 | X3762 == -7 | X3762 == 20	

// money market accounts refers to: money market accounts, floating rate savings account
gen mm = 0
replace mm = X3730 if X3732 == 4 | X3732 == 0  | X3732 == 12
replace mm = mm + X3736 if X3738 == 4 | X3738 == 0 |  X3738 == 12
replace mm = mm + X3742 if X3744 == 4 | X3744 == 0 |  X3744 == 12
replace mm = mm + X3748 if X3750 == 4 | X3750 == 0 |  X3750 == 12
replace mm = mm + X3754 if X3756 == 4 | X3756 == 0 |  X3756 == 12
replace mm = mm + X3760 if X3762 == 4 | X3762 == 0 |  X3762 == 12

// percentage of savings accounts invested in stocks	
gen savings_ps = X7075*X3730/savings if X3732 == 1 | X3732 == 2 | X3732 == 5 | ///
	X3732 == 6 | X3732 == 14 | X3732 == 0 | X3732 == -7 | X3732 == 20
replace savings_ps = savings_ps + X7078* X3736/savings if X3738 == 1 | X3738 == 2 | X3738 == 5 | ///
	X3738 == 6 | X3738 == 14 | X3738 == 0 | X3738 == -7 | X3738 == 20
replace savings_ps = savings_ps + X7081*X3742/savings if X3744 == 1 | X3744 == 2 | X3744 == 5 | ///
	X3744 == 6 | X3744 == 14 | X3744 == 0 | X3744 == -7 | X3744 == 20
replace savings_ps = savings_ps + X7084*X3748/savings if X3750 == 1 | X3750 == 2 | X3750 == 5 | ///
	X3750 == 6 | X3750 == 14 | X3750 == 0 | X3750 == -7 | X3750 == 20
replace savings_ps = savings_ps + X7087*X3754/savings if X3756 == 1 | X3756 == 2 | X3756 == 5 | ///
	X3756 == 6 | X3756 == 14 | X3756 == 0 | X3756 == -7 | X3756 == 20
replace savings_ps = savings_ps +	X7090*X3760/savings if X3762 == 1 | X3762 == 2 | X3762 == 5 | ///
	X3762 == 6 | X3762 == 14 | X3762 == 0 | X3762 == -7 | X3762 == 20

replace savings_ps = 0 if savings_ps == . & savings == 0 
	
// percentage of money market accounts invested in stocks	
gen mm_ps = X7075*X3730/mm if X3732 == 4 | ///
 X3732 == 0  | X3732 == 12
replace mm_ps = mm_ps + X7078*X3736/mm if X3738 == 4 ///
| X3738 == 0 | X3738 == 12
replace mm_ps = mm_ps + X7081*X3742/mm if X3744 == 4 ///
| X3744 == 0 | X3744 == 12
replace mm_ps = mm_ps + X7084*X3748/mm if X3750 == 4 ///
| X3750 == 0 | X3750 == 12
replace mm_ps = mm_ps + X7087*X3754/mm if X3756 == 4 ///
| X3756 == 0 | X3756 == 12
replace mm_ps = mm_ps + X7090*X3760/mm if X3762 == 4 ///
| X3762 == 0 | X3762 == 12

replace mm_ps = 0 if mm_ps == . & mm == 0 

// additional account (cannot see wheter mm or savings, assume same share)
gen help = savings / (savings + mm) // assume same split savings vs. mm as for other account
replace help = 0.5 if help == . // when no other savings or mm, assume 50/50 split

replace X3765 = 0 if X3765 < -1
replace savings = savings + help*X3765 
replace mm = mm + (1-help)*X3765
drop help

// mutual funds
ren X6704 totmutf  // market value total mutual funds or hedge funds
ren X3822 stmutf   // market value of all stock mutual funds and ETFs
ren X3824 tfbmutf  // market value of tax free bond mutual funds and ETFs
ren X3826 gbmutf   // market value of US gov backed bond mutual funds and ETFs
ren X3828 obmutf   // market value of other mutual funds and ETFs
ren X3830 comutf   // market value of combination funds and ETFs
ren X7787 omutf    // market value of all other funds

// bonds
ren X3902 savbnd   // total value of all savings bonds
ren X7635 mortbnd  // total value of mortgage backed bonds
ren X7637 notxbnd  // total value of tax free bonds (such as municipal bonds)
ren X7636 govtbnd  // US Government bonds or treasury bills

// total value of other bonds (corporate bonds and foreign bonds)
replace X7639 = 0 if X7639 < 0
replace X7638 = 0 if X7638 < 0
gen obnd = X7639 + X7638 

ren X3906 mortbndf // face value of mortgage backed bonds  
ren X3908 govtbndf // face value of governemnt backed bonds 
ren X3910 notxbndf // face value of non-taxable bonds 

replace X7634 = 0 if X7634 < 0
replace X7632 = 0 if X7632 < 0
gen obndf = X7634 + X7632  // face value of corporate and foreign bonds


// stocks (total market value)
ren X3915 stocks

// dollar value of cash or call money accounts
replace X3930 = 0 if 3930 == -1 
ren X3930 call 

// annuities: how much would you receive if cashed in?
ren X6577 annuit 
// trusts/managed investment account: how much receive if cashed in?
ren X6587 trusts

foreach var of varlist X6578 X6580 X6588 X6590 {
replace `var' = 0 if `var' <0
}

// how much income from annuities or trusts that you could or could not cash in
gen annuit_pay =  X6578 + X6580
gen trusts_pay =  X6588 + X6590

foreach var of varlist X6592 X6582 {
replace `var' = 0 if `var' <0	
}

// percent of trusts/annuities invested in stocks 
ren X6592 trusts_ps 
ren X6582 annuit_ps

// how is trust/ annuity invested? (all in stocks, all in bonds etc.)
ren X6591 trusts_invest
ren X6581 annuit_invest

// face value of all life insurance policies
ren X4003 li

// current cash value of these policies
replace X4006 = 0 if X4006 <0
ren X4006 cashli

// all together, how much are you owed?
ren X4018 owed 

// other assets
replace X4022 = 0 if X4022 < 0
replace X4026 = 0 if X4026 < 0 
replace X4030 = 0 if X4030 < 0  
gen othasset =  X4022 + X4026 + X4030 + owed

// what is the current value of the trust or foundation
replace X7661 = 0 if X7661 < 0
ren X7661 found

// pensions assets
foreach var of varlist X6963 X6969 X6975 X6981 X6934 X6938 X6942 X6946 ///
X11037 X11137 X11337 X11437 X5604 X5612 X5620 X5628 X6997 ///
X6462 X6467 X6472 X6477 X6957 X11032 X11132 X11332 ///
X11432 X11259 X11559 {
replace `var' = 0 if `var' == -1
} 

// future pensions: how much is in account now? (cumulated)
gen futpen  = X5604+X5612+X5620+X5628

// future pensions: percent invested in stocks: cumulated
gen futpen_ps = X6963*X5604/futpen + X6969 * X5612/futpen + ///
				X6975*X5620/futpen + X6981*X5628/futpen

// future pensions: how much is in account now -- indv. accounts
ren X5604 futpen1 
ren X5612 futpen2 
ren X5620 futpen3 
ren X5628 futpen4

// future pensions: percent invested in stocks: indivdiual accounts
ren X6963 futpen1_ps 
ren X6969 futpen2_ps
ren X6975 futpen3_ps 
ren X6981 futpen4_ps 

replace futpen_ps = 0 if futpen == 0 & futpen_ps ==.
foreach nmb of numlist 1/4 {
replace futpen`nmb'_ps = 0 if futpen`nmb' == 0 & futpen`nmb'_ps ==.
}

// future pensions: additional account
replace futpen = futpen + X6997
ren X6997 futpenadd

// future pensions: how invested (all in stocks, all in bonds, etc.)
ren X6962 futpen_invest1
ren X6968 futpen_invest2
ren X6974 futpen_invest3
ren X6980 futpen_invest4

// current pensions (refers to pensions currently received)
gen currpen = X6462 + X6467 + X6472 + X6477

// current benefit from pensions: percent invested in stocks
gen currpen_ps = X6934*X6462/currpen + X6938*X6467/currpen + ///
				 X6942*	X6472/currpen + X6946*X6477/currpen

replace currpen_ps = 0 if currpen == 0 & currpen_ps ==.
// current pensions: addtional account
replace currpen = currpen + X6957

// current pensions: how invested (all in stocks, all in bonds, etc.)
ren X6933 currpen_invest1
ren X6937 currpen_invest2
ren X6941 currpen_invest3
ren X6945 currpen_invest4

// current job pensions (including thrifts): balance on accounts (cumulated)
gen thrift = X11032 + X11132 + X11332 + X11432

// current job pensions (including thrifts): balance on individual accounts
ren X11032 thrift1
ren X11132 thrift2
ren X11332 thrift3
ren X11432 thrift4


// current job pension: percent invested in stocks (cumulated)
gen thrift_ps = X11037*thrift1/thrift + X11137*thrift2/thrift + ///
				 X11337*thrift3/thrift + X11437*thrift4/thrift

// current job pension: percent invested in stocks (individual accounts)
ren X11037 thrift1_ps			 
ren X11137 thrift2_ps
ren X11337 thrift3_ps
ren X11437 thrift4_ps
foreach nmb of numlist 1/4 {
replace thrift`nmb'_ps = 0 if thrift`nmb' == 0 & thrift`nmb'_ps ==.	
}			 

// current job pensions: remaining accounts
replace thrift = thrift + X11259 + X11559
ren X11259 thrift7
ren X11559 thrift8

// how are current job pensions invested (all in stocks, all in bonds, etc.)
ren X11036 thrift_invest1
ren X11136 thrift_invest2
ren X11336 thrift_invest3
ren X11436 thrift_invest4

// further clean variables

drop X* J*

replace sexsp   = . if sexsp == 0
replace agesp   = . if agesp == 0
replace martsp1 = . if martsp1 == 0
replace educsp  = . if educsp == 0
replace martsp  = . if martsp == 0
replace bysp    = . if bysp == 0

replace trusts_pay  = 0 if trusts_pay < 0
replace annuit_pay  = 0 if annuit_pay < 0 
replace checking    = 0 if checking < 0
replace prepaid     = 0 if prepaid < 0 
replace call        = 0 if call == -1
replace cashli      = 0 if cashli == -1
replace found       = 0 if found == -1

foreach x in educref educsp {
replace `x' = . if `x' == -1 // code "less than first grade" as missing
replace `x' = 7 if `x' == 8  // high school with diploma and without in one category
replace `x' = 8 if `x' > 8   // some college or above 
}

global loop  IRAKH_ref IRAKH_sp IRAKH_oth annuit trusts savings mm thrift currpen futpen ///
futpen1 futpen2 futpen3 futpen4 thrift1 thrift2 thrift3 thrift4

foreach var of varlist $loop {
replace `var'_ps = 0 if `var'_ps == -1
replace `var'_ps = `var'_ps/10000 
}

gen year = 2016 

save "$scfwkddata/2016_adj.dta", replace 

/////////////////////////////////////////////////////////////////
//////////////////////// waves 2013-2001 ////////////////////////
/////////////////////////////////////////////////////////////////


foreach num in 13 10 07 04 01 {

use "$scfrawdata/20`num'/p`num'i6.dta", clear

// HH characteristics
ren X101  hh_num   // number of people in HH
ren X7001 peu_num  // number of people in PEU
ren X8000 rev      // orginal spouse head variables reversed

// weights
ren X42001 wgt_rv

// demographic variables
ren X8021 sexref  // sex of reference person
ren X103  sexsp   // sex of spouse

ren X5908 byref     // year of birth reference person
ren X6108 bysp		// year of birth spouse

ren X8022 ageref	// age reference person
ren X104  agesp     // age spouse

ren X8023 martref1  // marital status reference person (1. married, 2. living with partner, 3. separated, 4. divorced, 5. widowed, 6. never married )
ren X105  martsp1   // marital status spouse (1. married, 2. living with partner, 3. separated, 4. divorced, 5. widowed, 6. never married )

ren X5901 educref  // highest level of education, reference person
ren X6101 educsp   // highest level of education, spouse

ren X7372 martref   // legal marital status, reference person (1. married, 3. separated, 4. divorced, 5. widowed, 6. never married)
ren X7018 mart2sp   // legal marital status, spouse (1. married, 3. separated, 4. divorced, 5. widowed, 6. never married)

ren X7402 indus_ref  // industry: reference person
ren X7412 indus_sp   // industry: spouse
ren X7401 occup_ref  // occupation: reference person
ren X7411 occup_sp   // occupation: spouse

// inheritances and gifts
ren X5801 inher_d   // Have you or your spouse been giving substatial gifts or inheritances? (reference person)

ren X5803 inher1    // Was the first transfer an inhertiance, trust, gift?
ren X5808 inher2	// Was the second transfer an inhertiance, trust, gift?
ren X5813 inher3 	// Was the third transfer an inhertiance, trust, gift?

ren X5804 inher1_val  // Approximate value at the time received, first transfer 
ren X5809 inher2_val  // Approximate value at the time received, second transfer
ren X5814 inher3_val  // Approximate value at the time received, third transfer

ren X5805 inher1_year    // Year received, first transfer 
ren X5810 inher2_year    // Year received, second transfer
ren X5815 inher3_year    // Year received, third transfer

ren X5818 inheroth_val  // Amount of other transfers received 

ren X5819 inher_exp     // Do you or your partner expect to receive substantial gift or inheritance in the future?
ren X5821 inher_expamt  // About how much do you expect?

ren X5824 inher_imp      // Do you consider leaving an inhertitance as important?
ren X5825 inher_expown   // Do you or your spouse expect to leave a sizable estate?

// no of children and grandchildren in HH, no age restriction (i.e. also adult chilren in HH counted as children)
gen numkids = 0
foreach x in 102 108 114 120 126 132 202 208 214 220 226 {
capture confirm variable X`x
    if !_rc {
        display "X`x' exists"
		replace numkids = numkids + 1 if X`x'== 4 | X`x' == 5 
    }
    else {
        display "X`x' does not exist"
    } 
}

// to number of children in household, add children living elsewhere
replace X5910 = 0 if X5910 == -1
replace numkids = numkids + X5910

// no of children in HH  + children living elsewhere below 25
replace X5911 = 0 if X5911 == -1
gen numkids25 = numkids - X5911 if X5911 != -2 & X5911 != .
replace numkids25 = numkids - X5910 if X5911 == -2


// total family income 
ren X5702 finc_wage        // annual income from wages and salaries 
ren X5704 finc_business    // annual income from farm or proprietorships 
ren X5706 finc_ntaxinv     // annual income from non-taxable investments
ren X5708 finc_interest    // annual income from interest
ren X5710 finc_dividend    // annual income from dividends
ren X5712 finc_stocks      // annual income from sales of stocks or mutual funds
ren X5714 finc_otherbusi   // annual income from other business or investments
ren X5716 finc_compen      // annual income from unemployment or worker's compensation
ren X5718 finc_childsup    // annual income from child support or alimony payments
ren X5722 finc_socials     // annual income from social security (pensions, annuities, etc.)
ren X5720 finc_welfare     // annual income from welfare (TANF, SNAP, etc.)
ren X5724 finc_other       // annual income from other sources

// recode of variables
foreach x of varlist finc_business finc_otherbusi finc_stocks finc_other {
    replace `x' = 0 if `x' == -9 | `x' == -2 | `x' == -1 
}

// add withdrawls from IRAs to other income (included in other income before 2004)
if `num' >= 04 {
* withdrawls from reference person
replace finc_other=finc_other+X6558+X6566+X6574 
* withdrawles from spouse and future benefits
foreach x in X6464 X6469 X6474 X6479 X6965 X6971 X6977 X6983 {
replace finc_other= finc_other + `x' if `x' >=0
}
}

// generate income measures
gen totinc_nocap = finc_wage + finc_compen + finc_childsup + finc_socials + ///
				   finc_welfare + finc_other

gen totinc_nocapoth = finc_wage + finc_compen + finc_childsup + finc_socials + ///
				   finc_welfare 

// non-financial assets: value of vehicles
* personally owned cars (first four)
gen vehic =X8166+X8167+X8168+X8188
* personally owned cars (all others combined)
replace vehic = vehic +  X2422 
* other owned vehicles (motor home, RV, motorcycle, boat, airplane)
replace vehic = vehic + X2506+X2606+X2623

// non-financias assets: primary residence (excluding mortgages)
ren X513 hval_farm1 // land value of farm
ren X526 hval_farm2 // land value of farm if operated as business

ren X507 farm_frac  // fraction of property used for farming
replace farm_frac = 0 if farm_frac < 0

ren X604 hval_mh1   // value of mobile home (only own site)
ren X614 hval_mh2   // value of mobile home (only own home)
ren X623 hval_mh3   // value of mobile home (own both)
ren X716 hval_house // value of home or apartment
replace hval_house = 0 if hval_house < 0

// home ownership variables
ren X501 hometype       // type of principle residence 
ren X508 owntype_farm   // ownership status of farm 
ren X601 owntype_mob    // ownership status of mobile home
ren X701 owntype_oth    // ownership status of other type of housing (house, apartment etc.)

// non-financial assets: further compenents

// non-financial assets: other residential real estate (including land contracts)

if `num' == 13 {
// land contracts and notes given by respondent
gen land_contracts = max(X1306,X1310) +max(X1325,X1329) +X1339 

//first and second property other than primary residence
gen oth_prop1 = X1706*(X1705/10000)  // multiply with percentage owned
gen oth_prop2 = X1806*(X1805/10000)

// remaining vacation properties
gen vac_prop = X2002

// all remaining properties other than vacation properties
replace X2012 = 0 if X2012 < 0
gen oth_prop3 = X2012
}

if `num' == 10 {
// land contracts and notes given by respondent
gen land_contracts = max(X1405,X1409)+max(X1505,X1509)+X1619 

// first and second property other than primary residence
gen oth_prop1 = X1706*(X1705/10000)  // multiply with percentage owned
gen oth_prop2 = X1806*(X1805/10000)

// remaining vacation properties
gen vac_prop = X2002

// all remaining properties other than vacation properties
replace X2012 = 0 if X2012 < 0
gen oth_prop3 = X2012
}


if `num' < 10 {
// land contracts and notes given by respondent
gen land_contracts = max(X1405,X1409)+max(X1505,X1509)+max(X1605,X1609)+X1619

// first, second, and third property other than primary residence (after 2007: only asked for first two)
gen oth_prop1 = X1706*(X1705/10000) // multiply with percentage owned
gen oth_prop2 = X1806*(X1805/10000) +  X1906*(X1905/10000)

// remaining vacation properties
gen vac_prop = X2002

// all remaining properties other than vacation properties
replace X2012 = 0 if X2012 < 0
gen oth_prop3 = X2012
}


// home equity lines of credit (first, second, and third line)
gen heloc = 0
replace heloc = heloc + X1108 if X1103 == 1
replace heloc = heloc + X1119 if X1114 == 1
replace heloc = heloc + X1130 if X1125 == 1

*fourth home equity line of credit: assume same fraction secured by home equity as for first three (above)
replace heloc = heloc + max(0,X1136)*(heloc/(X1108+X1119+X1130)) if heloc > 0
*fourth home equity line of credit: if not information on firt three, assume 50% secured by home equity
replace heloc = heloc + max(0,X1136)*0.5 if heloc == 0

// mortgages/ land contract/ equity loans on principle residence
gen nh_mort = X805+X905+X1005
gen mrthel = nh_mort + heloc

// debt: other residential debt (not secured by primary residence)
if `num' == 13 {
gen resdbt =X1318+X1337+X1342+X2006 + X1715*(X1705/10000) + X1815*(X1805/10000)+X2016
}

if `num' == 10 {
gen resdbt =X1417+X1517+X1621+X2006 + X1715*(X1705/10000) + X1815*(X1805/10000)+X2016
}

if `num' <= 07 {
gen resdbt =X1417+X1517+X1617+X1621+X2006 + X1715*(X1705/10000) + X1815*(X1805/10000)+X2016 + X1915*(X1905/10000)
}

// debt: Credit Card Balances 
if `num' >= 10 {
foreach var of varlist X413 X421 X427 X430 {
replace `var' = 0 if `var' < 0	
}
gen ccbal = X413 + X421 + X427 + X430 + X7575
}

if `num' < 10 {
foreach var of varlist X413 X421 X424 X427 X430 {
replace `var' = 0 if `var' < 0	
}
gen ccbal = X413 + X421 + X427 + X430 + X424 + X7575
}

// vechicle loans: personally owned cars and other owned vehicles
gen veh_loan  = X2218 + X2318 + X2418 + X7169 + X2424 + X2519 + X2619 + X2625

// education loans: first six, and then all remaining together 
gen educ_loan = X7824 + X7847 + X7870 + X7924 + X7947 + X7970 + X7179 

// add consumer loans into education loans if purpose was education
replace educ_loan = educ_loan + X2723 if X2710 == 83
replace educ_loan = educ_loan + X2740 if X2727 == 83
replace educ_loan = educ_loan + X2823 if X2810 == 83
replace educ_loan = educ_loan + X2840 if X2827 == 83
replace educ_loan = educ_loan + X2923 if X2910 == 83
replace educ_loan = educ_loan + X2940 if X2927 == 83

// other installment loans 
gen oth_install =  X7183 + X1044 + X1215 + X1219

// add consumer loans if purpose is other
replace oth_install = oth_install + X2723 if X2710 == 78
replace oth_install = oth_install + X2740 if X2727 == 78
replace oth_install = oth_install + X2823 if X2810 == 78
replace oth_install = oth_install + X2840 if X2827 == 78
replace oth_install = oth_install + X2923 if X2910 == 78
replace oth_install = oth_install + X2940 if X2927 == 78

// add to overall variable "installment loans"
gen inst_loan = veh_loan + educ_loan + oth_install 


// debt: other lines of credit ( lines of credit that are not secured by equity of home)
gen othloc = 0 
replace othloc = othloc + X1108 if X1103 != 1
replace othloc = othloc + X1119 if X1114 != 1
replace othloc = othloc + X1130 if X1125 != 1

*fourth line of credit: assume same fraction not secured by home equity as for first three (above)
replace othloc = max(0,X1136)*(othloc/(X1108+X1119+X1130)) if othloc > 0 
*fourth line of credit: if not information on firt three credti lines, assume 50% not secured by home equity
replace othloc = max(0,X1136)*0.5   if othloc == 0

// debt: other debt
gen odebt = 0

if `num' >= 10 {
replace odebt = odebt + max(0,X11027) if X11070==5 // if not recorded earlier interview
replace odebt = odebt + max(0,X11127) if X11170==5
replace odebt = odebt + max(0,X11327) if X11370==5
replace odebt = odebt + max(0,X11427) if X11470==5

// add: borrowed on life insurance, miscellanous debts, margin loans
replace odebt = odebt + max(0,X4010) + max(0,X4032) + max(0,X3932)
}

if `num' == 07 | `num' == 04 {
replace odebt = odebt + max(0,X11027) if X11070==5 // if not recorded earlier interview
replace odebt = odebt + max(0,X11127) if X11170==5
replace odebt = odebt + max(0,X11227) if X11270==5
replace odebt = odebt + max(0,X11327) if X11370==5
replace odebt = odebt + max(0,X11427) if X11470==5
replace odebt = odebt + max(0,X11527) if X11570==5

// add: borrowed on life insurance, miscellanous debts, margin loans
replace odebt = odebt + max(0,X4010) + max(0,X4032) + max(0,X3932)
}

if `num' < 04 {
replace odebt = odebt + max(0,X4229) if X4230==5 // if not recorded earlier interview
replace odebt = odebt + max(0,X4329) if X4330==5
replace odebt = odebt + max(0,X4429) if X4430==5
replace odebt = odebt + max(0,X4829) if X4830==5
replace odebt = odebt + max(0,X4929) if X4930==5
replace odebt = odebt + max(0,X5029) if X5030==5

// add: borrowed on life insurance, miscellanous debts, margin loans
replace odebt = odebt + max(0,X4010) + max(0,X4032) + max(0,X3932)
}

///// FINANCIAL ASSETS /////

// checking accounts
foreach var of varlist X3506 X3510 X3514 X3518 X3522 X3526 X3529 {
replace `var' = 0 if `var' < 0
}
gen checking = X3506 + X3510 + X3514 + X3518 + X3522 + X3526 + X3529

// prepaid debit card or prepaid government benefit card
gen prepaid = . // not available before 2016

// IRA/KEOGH accounts (roth IRAs, roll-over IRAs, regular IRAs, Keogh)
if `num' > 01 {
foreach var of varlist X6551 X6559 X6567 X6552 X6560 X6568 X6553 ///
		    X6561 X6569 X6554 X6562 X6570  {
replace `var' = 0 if `var' < 0
}		
	
gen IRAKH = X6551 + X6559 + X6567 + X6552 + X6560 + X6568 + X6553 + ///
		    X6561 + X6569 + X6554 + X6562 + X6570

// accounts referring to reference person	
gen IRAKH_ref = X6551 + X6552 + X6553 + X6554
// accounts referring to spouse
gen IRAKH_sp = X6559 + X6560 + X6561 + X6562
// accounts referring to other familiy members 
gen IRAKH_oth = X6567 + X6568 + X6569 + X6570

// percent of accounts invested in stocks
foreach var of varlist X6556 X6564 X6572 {
replace `var' = 0 if `var' < 0
}			
ren X6556 IRAKH_ref_ps
ren X6564 IRAKH_sp_ps
ren X6572 IRAKH_oth_ps

// how is the money in accounts invested (all in stocks, all in bonds etc.)
ren X6555 IRAKH_ref_invest
ren X6563 IRAKH_sp_invest
ren X6571 IRAKH_oth_invest
}

if `num' == 01 {
foreach var of varlist X3610 X3620 X3630 {
replace `var' = 0 if `var' < 0
}

gen IRAKH = X3610 + X3620 + X3630
// accounts referring to reference person	
gen IRAKH_ref = X3610
// accounts referring to spouse
gen IRAKH_sp = X3620
// accounts referring to other familiy members
gen IRAKH_oth = X3630
// how is the money in accounts invested (all in stocks, all in bonds etc.)
ren X3631 IRAKH_invest
}

// certificates of deposits (cds)
ren X3721 cds

// savings + money market accounts 
if `num' > 01 {
foreach var of varlist X3730 X3736 X3742 X3748 X3754 X3760 X3765 {
replace `var' = 0 if `var' < 0
}

// savings account refers to: traditional savings account, coverdell/education IRA/KEOGH
// savings account for designated purpose, share accoutn, informal group savings arrangement
// other, and foreign account type

gen savings = 0
replace savings = X3730 if X3732 == 1 | X3732 == 2 | X3732 == 5 | ///
	X3732 == 6 | X3732 == 14 | X3732 == 0 | X3732 == -7 | X3732 == 20
replace savings = savings + X3736 if X3738 == 1 | X3738 == 2 | X3738 == 5 | ///
	X3738 == 6 | X3738 == 14 | X3738 == 0 | X3738 == -7 | X3738 == 20
replace savings = savings + X3742 if X3744 == 1 | X3744 == 2 | X3744 == 5 | ///
	X3744 == 6 | X3744 == 14 | X3744 == 0 | X3744 == -7 | X3744 == 20
replace savings = savings + X3748 if X3750 == 1 | X3750 == 2 | X3750 == 5 | ///
	X3750 == 6 | X3750 == 14 | X3750 == 0 | X3750 == -7 | X3750 == 20
replace savings = savings + X3754 if X3756 == 1 | X3756 == 2 | X3756 == 5 | ///
	X3756 == 6 | X3756 == 14 | X3756 == 0 | X3756 == -7 | X3756 == 20
replace savings = savings + X3760 if X3762 == 1 | X3762 == 2 | X3762 == 5 | ///
	X3762 == 6 | X3762 == 14 | X3762 == 0 | X3762 == -7 | X3762 == 20
	
// money market accounts refers to: money market accounts, floating rate savings account
gen mm = 0
replace mm = X3730      if X3732 == 4 | X3732 == 0 | X3732 == 12
replace mm = mm + X3736 if X3738 == 4 | X3738 == 0 | X3738 == 12
replace mm = mm + X3742 if X3744 == 4 | X3744 == 0 | X3744 == 12
replace mm = mm + X3748 if X3750 == 4 | X3750 == 0 | X3750 == 12
replace mm = mm + X3754 if X3756 == 4 | X3756 == 0 | X3756 == 12
replace mm = mm + X3760 if X3762 == 4 | X3762 == 0 | X3762 == 12


if `num' > 04 {

foreach var of varlist X7075 X7078 X7081 X7084 X7087 X7090 {
replace `var' = 0 if `var' == -1
}

// percentage of savings accounts invested in stocks
gen savings_ps = X7075*X3730/savings if X3732 == 1 | X3732 == 2 | X3732 == 5 | ///
	X3732 == 6 | X3732 == 14 | X3732 == 0 | X3732 == -7 | X3732 == 20
replace savings_ps = savings_ps + X7078* X3736/savings if X3738 == 1 | X3738 == 2 | X3738 == 5 | ///
	X3738 == 6 | X3738 == 14 | X3738 == 0 | X3738 == -7 | X3738 == 20
replace savings_ps = savings_ps + X7081*X3742/savings if X3744 == 1 | X3744 == 2 | X3744 == 5 | ///
	X3744 == 6 | X3744 == 14 | X3744 == 0 | X3744 == -7 | X3744 == 20
replace savings_ps = savings_ps + X7084*X3748/savings if X3750 == 1 | X3750 == 2 | X3750 == 5 | ///
	X3750 == 6 | X3750 == 14 | X3750 == 0 | X3750 == -7 | X3750 == 20
replace savings_ps = savings_ps + X7087*X3754/savings if X3756 == 1 | X3756 == 2 | X3756 == 5 | ///
	X3756 == 6 | X3756 == 14 | X3756 == 0 | X3756 == -7 | X3756 == 20
replace savings_ps = savings_ps +	X7090*X3760/savings if X3762 == 1 | X3762 == 2 | X3762 == 5 | ///
	X3762 == 6 | X3762 == 14 | X3762 == 0 | X3762 == -7 | X3762 == 20

replace savings_ps = 0 if savings_ps == . & savings == 0

// percentage of money market accounts invested in stocks		
gen mm_ps = X7075*X3730/mm if X3732 == 4 | ///
 X3732 == 0 | X3732 == 12
replace mm_ps = mm_ps + X7078*X3736/mm if X3738 == 4 ///
| X3738 == 0 | X3738 == 12
replace mm_ps = mm_ps + X7081*X3742/mm if X3744 == 4 ///
| X3744 == 0 | X3744 == 12
replace mm_ps = mm_ps + X7084*X3748/mm if X3750 == 4 ///
| X3750 == 0 | X3750 == 12
replace mm_ps = mm_ps + X7087*X3754/mm if X3756 == 4 ///
| X3756 == 0 | X3756 == 12
replace mm_ps = mm_ps + X7090*X3760/mm if X3762 == 4 ///
| X3762 == 0 | X3762 == 12

replace mm_ps = 0 if mm_ps == . & mm == 0
}

// additional account (cannot see wheter mm or savings, assume same share)
gen help = savings / (savings + mm) // assume same split savings vs. mm as for other account
replace help = 0.5 if help == .     // when no other savings or mm, assume 50/50 split

replace savings = savings + help*X3765 
replace mm = mm + (1-help)*X3765
drop help

}

if `num' == 01 {
foreach var of varlist X3706 X3711 X3716 X3718 ///
		X3804 X3807 X3810 X3813 X3816 X3818	{
replace `var' = 0 if `var' < 0
}

// In 2001 money market and savings accounts were different sections of questionnaires
// money market accounts
gen mm = X3706 + X3711 + X3716 + X3718
// savings accounts
gen savings = X3804 + X3807 + X3810 + X3813 + X3816 + X3818

}

// mutual funds
ren X6704 totmutf  // market value total mutual funds or hedge funds
ren X3822 stmutf   // market value of all stock mutual funds and ETFs
ren X3824 tfbmutf  // market value of tax free bond mutual funds and ETFs
ren X3826 gbmutf   // market value of US gov backed bond mutual funds and ETFs
ren X3828 obmutf   // market value of other mutual funds and ETFs
ren X3830 comutf   // market value of combination funds and ETFs

if `num' > 01 {
ren X7787 omutf    // market value of all other funds
}

// bonds
ren X3902 savbnd   // total value of all savings bonds
ren X7635 mortbnd  // total value of mortgage backed bonds
ren X7637 notxbnd  // total value of tax free bonds (such as municipal bonds)
ren X7636 govtbnd  // US Government bonds or treasury bills

// total value of other bonds (corporate bonds and foreign bonds)
replace X7639 = 0 if X7639 < 0
replace X7638 = 0 if X7638 < 0
gen obnd = X7639 + X7638 

ren X3906 mortbndf // face value of mortgage backed bonds 
ren X3908 govtbndf // face value of governemnt backed bonds 
ren X3910 notxbndf // face value of non-taxable bonds

replace X7634 = 0 if X7634 < 0
replace X7632 = 0 if X7632 < 0
gen obndf = X7634 + X7632 // face value of corporate and foreign bonds

// stocks (total market value)
ren X3915 stocks

// dollar value of cash or call money accounts
replace X3930 = 0 if 3930 == -1 
ren X3930 call 

if `num' > 01 {
// annuities: how much would you receive if cashed in?
ren X6577 annuit
// trusts/managed investment account: how much receive if cashed in?
ren X6587 trusts

foreach var of varlist X6578 X6580 X6588 X6590 {
replace `var' = 0 if `var' < 0
}

// how much income from annuities or trusts that you could or could not cash in
gen annuit_pay =  X6578 + X6580
gen trusts_pay = X6588 + X6590
}

if `num' == 01 {
// annuities: total dollar value
ren X6820 annuit
// trusts/managed investment account: total dollar value
ren X6835 trusts
// trusts/managed investment account: how much did you receive from these annuities?
gen annuit_pay = X6817 
gen trusts_pay = X6832
}

if `num' > 01 {
	
foreach var of varlist X6592 X6582 {
replace `var' = 0 if `var' <0	
}
// percent of trusts/annuities invested in stocks 	
ren X6592 trusts_ps 
ren X6582 annuit_ps

// how is trust/ annuity invested? (all in stocks, all in bonds etc.)
ren X6591 trusts_invest
ren X6581 annuit_invest
}

if `num' == 01 {
	// how is trust/ annuity invested? (all in stocks, all in bonds etc.)
ren X6841 trusts_invest 
ren X6826 annuit_invest
}

// face value of all life insurance policies
ren X4003 li

// current cash value of these policies
replace X4006 = 0 if X4006 <0
ren X4006 cashli

// all together, how much are you owed?
ren X4018 owed 

// other assets
foreach var of varlist X4022 X4026 X4030 {
replace `var' = 0 if `var' <0
}
gen othasset =  X4022 + X4026 + X4030 + owed 

// what is the current value of the trust or foundation (availalbe from 2007)
if `num' > 04 {
ren X7661 found
}
if `num' <= 04 {
gen found = .
}

// pensions assets

// future pensions: how much is in account now? (cumulated)
foreach var of varlist X5604 X5612 X5620 X5628 {
replace `var' = 0 if `var' <0
}
gen futpen  = X5604+X5612+X5620+X5628

// future pensions: how much is in account now -- indv. accounts
ren X5604 futpen1 
ren X5612 futpen2 
ren X5620 futpen3 
ren X5628 futpen4


// add two additional accounts that were individually recorded until 2007
if `num' <= 07 {
foreach var of varlist X5636 X5644 {
replace `var' = 0 if `var' <0
}
replace futpen = futpen + X5636 + X5644
ren X5636 futpen5 
ren X5644 futpen6
}

if `num' > 01 {
// future pensions: how invested (all in stocks, all in bonds, etc.)
ren X6962 futpen_invest1
ren X6968 futpen_invest2
ren X6974 futpen_invest3
ren X6980 futpen_invest4

if `num' <= 07 {
ren X6986 futpen_invest5
ren X6992 futpen_invest6
}

// future pensions: percent invested in stocks: cumulated
foreach var of varlist X6963 X6969 X6975 X6981 {
replace `var' = 0 if `var' < 0
}
gen futpen_ps = X6963*futpen1/futpen + X6969 * futpen2/futpen + ///
				X6975*futpen3/futpen + X6981*futpen4/futpen

// future pensions: percent invested in stocks: indivdiual accounts
ren X6963 futpen1_ps 
ren X6969 futpen2_ps
ren X6975 futpen3_ps 
ren X6981 futpen4_ps 

foreach nmb of numlist 1/4 {
replace futpen`nmb'_ps = 0 if futpen`nmb' == 0 & futpen`nmb'_ps ==.
}

// add two additional accounts for 2007				
if `num' <= 07 {

foreach var of varlist X6987 X6993 {
replace `var' = 0 if `var' == -1
}
replace futpen_ps = futpen_ps + X6987*futpen5/futpen + X6993*futpen6/futpen
ren X6987 futpen5_ps 
ren X6993 futpen6_ps 
foreach nmb of numlist 5/6 {
replace futpen`nmb'_ps = 0 if futpen`nmb' == 0 & futpen`nmb'_ps ==.
}
}

replace futpen_ps = 0 if futpen == 0 & futpen_ps == .
}

if `num' == 01 {
// future pensions: how invested (all in stocks, all in bonds, etc.)
ren X6491 futpen_invest1 
ren X6492 futpen_invest2 
ren X6493 futpen_invest3 
ren X6494 futpen_invest4 
ren X6495 futpen_invest5 
ren X6496 futpen_invest6 
}

// future pensions: additional account
if `num' > 01 {
replace X6997 = 0 if X6997 < 0
replace futpen = futpen + X6997
ren X6997 futpenadd
}
			

// current pension (refers to pensions currently received)
foreach var of varlist X6462 X6467 X6472 X6477 {
replace `var' = 0 if `var' < 0
}
gen currpen = X6462 + X6467 + X6472 + X6477

// two additional accounts only individually recoreded until 2007
if `num' <= 07 {
replace X6482 = 0 if X6482 < 0
replace X6487 = 0 if X6487 < 0
replace currpen = currpen + X6482 + X6487
}

// current pensions: how invested (all in stocks, all in bonds, etc.)
if `num' > 01 {
ren X6933 currpen_invest1
ren X6937 currpen_invest2
ren X6941 currpen_invest3
ren X6945 currpen_invest4

if `num' <= 07 {
ren X6949 currpen_invest5
ren X6953 currpen_invest6
}

// current benefit from pensions: percent invested in stocks
foreach var of varlist X6934 X6938 X6942 X6946 {
replace `var' = 0 if `var' < 0
}
gen currpen_ps = X6934*X6462/currpen + X6938*X6467/currpen + ///
				 X6942*	X6472/currpen + X6946*X6477/currpen

// additional two accounts coded up until 2007				 
if `num' <= 07 {
foreach var of varlist X6950 X6954 {
replace `var' = 0 if `var' == -1
}
replace currpen_ps = currpen_ps + X6950*X6482/currpen + X6954*X6487/currpen
}
				 
replace currpen_ps = 0 if currpen == 0 & currpen_ps ==.
}

// current pensions: how invested (all in stocks, all in bonds, etc.)
if `num' == 01 {
ren X6463 currpen_invest1 
ren X6468 currpen_invest2 
ren X6473 currpen_invest3 
ren X6478 currpen_invest4 
ren X6483 currpen_invest5 
ren X6488 currpen_invest6 
}

// current pensions: addtional account
if `num' > 01  {
replace X6957 = 0 if X6957 < 0
replace currpen = currpen + X6957
}

// current job pensions (including thrifts)
if `num' > 01 {
foreach var of varlist X11037 X11137 X11337 X11437 ///
		X11032 X11132 X11332 X11432 {
replace `var' = 0 if `var'< 0
} 
// current job pensions (including thrifts): balance on accounts (cumulated)
gen thrift = X11032 + X11132 + X11332 + X11432

// current job pensions (including thrifts): balance on individual accounts
ren X11032 thrift1 
ren X11132 thrift2 
ren X11332 thrift3 
ren X11432 thrift4


// two additional accounts only individually recoreded until 2007
if `num' <= 07 {
replace X11232 = 0 if X11232 < 0
replace X11532 = 0 if X11532 < 0

replace thrift = thrift + X11232 + X11532

ren X11232 thrift5 
ren X11532 thrift6 
}

// current job pension: percent invested in stocks (cumulated)
gen thrift_ps = X11037*thrift1/thrift + X11137*thrift2/thrift + ///
				 X11337*thrift3/thrift + X11437*thrift4/thrift

// current job pension: percent invested in stocks (individual accounts)
ren X11037 thrift1_ps
ren X11137 thrift2_ps
ren X11337 thrift3_ps
ren X11437 thrift4_ps
foreach nmb of numlist 1/4 {				 
replace thrift`nmb'_ps = 0 if thrift`nmb' == 0 & thrift`nmb'_ps ==.
}

// two additional accounts only individually recoreded until 2007			 
if `num' <= 07 {
foreach var of varlist X11237 X11537 {
replace `var' = 0 if `var' < 0
}
replace thrift_ps = thrift_ps + X11237*thrift5/thrift + X11537*thrift6/thrift
ren X11237 thrift5_ps
ren X11537 thrift6_ps

foreach nmb of numlist 5/6 {				 
replace thrift`mb'_ps = 0 if thrift`nmb' == 0 & thrift`nmb'_ps ==.
}
}		 

// current job pensions: remaining accounts
replace X11259 = 0 if X11259 < 0
replace X11559 = 0 if X11559 < 0
replace thrift = thrift + X11259 + X11559
ren X11259 thrift7
ren X11559 thrift8

// how are current job pensions invested (all in stocks, all in bonds, etc.)
ren X11036 thrift_invest1
ren X11136 thrift_invest2
ren X11336 thrift_invest3
ren X11436 thrift_invest4

// two additional accounts only individually recoreded until 2007
if `num' <= 07 {
ren X11236 thrift_invest5
ren X11536 thrift_invest6
}
}

if `num' == 01 {
foreach var of varlist X4226 X4326 X4426 X4826 X4926 X5026 X4436 X5036 {
replace `var' = 0 if `var' < 0
}

// current job pensions (including thrifts): balance on accounts (cumulated)
gen thrift = X4226 + X4326 + X4426 + X4826 + X4926 + X5026 + X4436 + X5036

// current job pensions (including thrifts): balance on individual accounts
ren X4226 thrift1
ren X4326 thrift2
ren X4426 thrift3
ren X4826 thrift4
ren X4926 thrift5
ren X5026 thrift6
ren X4436 thrift7
ren X5036 thrift8

// how are current job pensions invested (all in stocks, all in bonds, etc.)
ren X4234 thrift_invest1
ren X4334 thrift_invest2
ren X4434 thrift_invest3
ren X4834 thrift_invest4
ren X4934 thrift_invest5
ren X5034 thrift_invest6
}

// further clean variables
drop X* J*

replace sexsp   = . if sexsp == 0
replace agesp   = . if agesp == 0
replace martsp1 = . if martsp1 == 0
replace educsp  = . if educsp == 0
replace martsp  = . if martsp == 0
replace bysp 	= . if bysp == 0

replace trusts_pay = 0 if trusts_pay < 0
replace annuit_pay = 0 if annuit_pay < 0 
replace checking   = 0 if checking < 0
replace prepaid    = 0 if prepaid < 0 
replace call       = 0 if call == -1
replace cashli     = 0 if cashli == -1
replace found      = 0 if found == -1

// recoding of education before 2016
foreach x in educref educsp {
replace `x' = . if `x' == -1   // code "less than first grade" as missing
foreach s of numlist 2/4 {
replace `x' = 1 if `x' == `s'  //  first to fourth grade combined
} 
foreach s of numlist 5/6 {
replace `x' = 2 if `x' == `s'  //  5th or 6th grade combined
}
foreach s of numlist 7/8 {
replace `x' = 3 if `x' == `s'  //  7th or 88th grade combined
}
replace `x' = 4 if `x' == 9    //  recode: 9th grade
replace `x' = 5 if `x' == 10   //  recode: 10th grade
replace `x' = 6 if `x' == 11   //  recode: 11th grade
replace `x' = 7 if `x' == 12   //  recode: 12th grade
replace `x' = 8 if `x' > 12    // some college or above 
}

if `num' > 07 {
global loop IRAKH_ref IRAKH_sp IRAKH_oth ///
trusts annuit savings mm thrift currpen futpen futpen1 futpen2 futpen3 futpen4 ///
thrift1 thrift2 thrift3 thrift4
}
if `num' <= 07 {
global loop IRAKH_ref IRAKH_sp IRAKH_oth ///
trusts annuit savings mm thrift currpen futpen futpen1 futpen2 futpen3 futpen4 futpen5 futpen6 ///
thrift1 thrift2 thrift3 thrift4 thrift5 thrift6
}

foreach var of varlist $loop {
    capture confirm variable `var'_ps, exact
    if !_rc {
        display "`var'_ps exists"
		replace `var'_ps = 0 if `var'_ps == -1
		replace `var'_ps = `var'_ps/10000 
    }
    else {
        display "`var'_ps does not exist"
    }
}

gen year = 20`num' 

save "$scfwkddata/20`num'_adj.dta", replace 
}

/////////////////////////////////////////////////////////////////
//////////////////////// waves 1998-1989 ////////////////////////
/////////////////////////////////////////////////////////////////

foreach num in 98 95 92 89 {
if `num' == 92 {
use "$scfrawdata/19`num'/p`num'i4.dta", clear
}
else {
use "$scfrawdata/19`num'/p`num'i6.dta", clear
}

if `num' == 89 {
ren X1 Y1
ren XX1 YY1
}

// HH characteristics
ren X101 hh_num    // number of people in HH
if `num' > 92 {
ren X7001 peu_num  // number of people in PEU
}
ren X8000 rev      // orginal spouse head variables reversed

// weights
ren X42001 wgt_rv

// demographic variables
ren X8021 sexref    // sex of reference person
ren X103  sexsp     // sex of spouse

ren X5908 byref     // year of birth reference person
ren X6108 bysp		// year of birth spouse

ren X8022 ageref	// age reference person
ren X104  agesp     // age spouse

ren X8023 martref1  // marital status reference person (1. married, 2. living with partner, 3. separated, 4. divorced, 5. widowed, 6. never married )
ren X105  martsp1   // marital status spouse (1. married, 2. living with partner, 3. separated, 4. divorced, 5. widowed, 6. never married )

ren X5901 educref // highest level of education, reference person
ren X6101 educsp  // highest level of education, spouse 

ren X7402 indus_ref  // industry: reference person
ren X7412 indus_sp   // industry: spouse
ren X7401 occup_ref  // occupation: reference person
ren X7411 occup_sp   // occupation: spouse

// inheritances and gifts
ren X5801 inher_d       // Have you or your spouse been giving substatial gifts or inheritances? (reference person)

ren X5803 inher1        // Was the first transfer an inhertiance, trust, gift?
ren X5808 inher2        // Was the second transfer an inhertiance, trust, gift?
ren X5813 inher3        // Was the third transfer an inhertiance, trust, gift?

ren X5804 inher1_val    // Approximate value at the time received, first transfer 
ren X5809 inher2_val    // Approximate value at the time received, second transfer
ren X5814 inher3_val    // Approximate value at the time received, third transfer

ren X5805 inher1_year    // Year received, first transfer 
ren X5810 inher2_year    // Year received, second transfer
ren X5815 inher3_year    // Year received, third transfer

ren X5818 inheroth_val  // Amount of other transfers received 

ren X5819 inher_exp     // Do you or your partner expect to receive substantial gift or inheritance in the future?
ren X5821 inher_expamt  // About how much do you expect?

ren X5824 inher_imp     // Do you consider leaving an inhertitance as important?
ren X5825 inher_expown  // Do you or your spouse expect to leave a sizable estate?

// no of children and grandchildren in HH, no age restriction (i.e. also adult chilren in HH counted as children)
gen numkids = 0
foreach x in 102 108 114 120 126 132 202 208 214 220 226 {
capture confirm variable X`x
    if !_rc {
        display "X`x' exists"
		replace numkids = numkids + 1 if X`x'== 4 | X`x' == 5 
    }
    else {
        display "X`x' does not exist"
    } 
}

// to number of children in household, add children living elsewhere
replace X5910 = 0 if X5910 == -1
replace numkids = numkids + X5910

// no of children in HH  + children living elsewhere below 25
replace X5911 = 0 if X5911 == -1
gen numkids25 = numkids - X5911 if X5911 != -2 & X5911 != .
replace numkids25 = numkids - X5910 if X5911 == -2

// total family income 
ren X5702 finc_wage			// annual income from wages and salaries
ren X5704 finc_business		// annual income from farm or proprietorships 			
ren X5706 finc_ntaxinv		// annual income from non-taxable investments
ren X5708 finc_interest		// annual income from interest
ren X5710 finc_dividend		// annual income from dividends
ren X5712 finc_stocks		// annual income from sales of stocks or mutual funds
ren X5714 finc_otherbusi	// annual income from other business or investments
ren X5716 finc_compen		// annual income from unemployment or worker's compensation
ren X5718 finc_childsup		// annual income from child support or alimony payments
ren X5722 finc_socials		// annual income from social security (pensions, annuities, etc.)
ren X5720 finc_welfare		// annual income from welfare (TANF, SNAP, etc.)
ren X5724 finc_other		// annual income from other sources

// recode of variables
foreach x of varlist finc_business finc_otherbusi finc_stocks finc_other {
    replace `x' = 0 if `x' == -9 | `x' == -2 | `x' == -1 
}

// generate income measures
gen totinc_nocap = finc_wage + finc_compen + finc_childsup + finc_socials + ///
				   finc_welfare + finc_other
				   
gen totinc_nocapoth = finc_wage + finc_compen + finc_childsup + finc_socials + ///
				   finc_welfare 

// non-financial assets: value of vehicles
if `num' >= 95 {
* personally owned cars (first four)
gen vehic = X8166+X8167+X8168+X8188
* personally owned cars (all others combined)
replace vehic = vehic + X2422
* other owned vehicles (motor home, RV, motorcycle, boat, airplane)
replace vehic = vehic + X2506 + X2606 + X2623
}
if `num' < 95 {
* personally owned cars (first three)
gen vehic = X8166+X8167+X8168
* personally owned cars (all others combined)
replace vehic = vehic + X2422
* other owned vehicles (motor home, RV, motorcycle, boat, airplane)
replace vehic = vehic + X2506 + X2606 + X2623
}	  
	  
// non-financial assets: primary residence (excluding mortgages)
ren X513 hval_farm1 // land value of farm (if respondent owns all)
ren X526 hval_farm2 // land value of farm if operated as business (if respondent owns part, refers to part R owns)

ren X507 farm_frac  // fraction of property used for farming
replace farm_frac = 0 if farm_frac < 0

ren X604 hval_mh1   // value of mobile home (only own site)
ren X614 hval_mh2   // value of mobile home (only own home)
ren X623 hval_mh3   // value of mobile home (own both)
ren X716 hval_house // value of home or apartment
replace hval_house = 0 if hval_house < 0	
	  
// home ownership variables
ren X501 hometype		// type of principle residence 
ren X508 owntype_farm	// ownership status of farm   
ren X601 owntype_mob	// ownership status of mobile home
ren X701 owntype_oth	// ownership status of other type of housing (house, apartment etc.)

// non-financial assets: further compenent

// land contracts and notes given by respondent
gen land_contracts = max(X1405,X1409)+max(X1505,X1509)+max(X1605,X1609)+max(0,X1619)

// first and second property other than primary residence
foreach var of varlist X1706 X1806 X1906 { 
replace `var' = 0 if `var' < 0	
}
gen oth_prop1 = X1706*(X1705/10000) // multiply with percentage owned 
gen oth_prop2 = X1806*(X1805/10000) +X1906*(X1905/10000)

// remaining vacation properties
gen vac_prop = X2002

// all remaining properties other than vacation properties
replace X2012 = 0 if X2012 < 0
gen oth_prop3 = X2012


// debt: home equity lines of credit (first, second, and third line)
gen heloc = 0
replace heloc = heloc + X1108 if X1103 == 1
replace heloc = heloc + X1119 if X1114 == 1
replace heloc = heloc + X1130 if X1125 == 1

*fourth home equity line of credit: assume same fraction secured by home equity as for first three (above)
replace heloc = heloc + max(0,X1136)*(heloc/(X1108+X1119+X1130)) if heloc > 0
*fourth home equity line of credit: if not information on firt three, assume 50% secured by home equity
replace heloc = heloc + max(0,X1136)*0.5 if heloc == 0

// mortgages/ land contract/ equity loans on principle residence
gen nh_mort = X805+X905+X1005
gen mrthel = nh_mort + heloc

// debt: other residential debt (not secured by primary residence)
gen resdbt =X1417+X1517+X1617+X1621+X2006 + X1715*(X1705/10000) + X1815*(X1805/10000)+X2016 + X1915*(X1905/10000)

// debt: Credit Card Balances 
if `num' >= 92 {
foreach var of varlist X413 X421 X424 X427 X430 {
replace `var' = 0 if `var' < 0	
}
gen ccbal = X413 + X421 + X427 + X430 + X424 + X7575
}

if `num' < 92 {
foreach var of varlist X413 X421 X424 X427 X430 {
replace `var' = 0 if `var' < 0	
}
gen ccbal = X413 + X421 + X427 + X430 + X424 
}


if `num' > 92 {
// vechicle loans: personally owned cars and other owned vehicles
gen veh_loan  = X2218 + X2318 + X2418 + X7169 + X2424 + X2519 + X2619 + X2625

// education loans: first six, and then all remaining together 
gen educ_loan = X7824 + X7847 + X7870 + X7924 + X7947 + X7970 + X7179 

// add consumer loans into education loans if purpose was education
replace educ_loan = educ_loan + X2723 if X2710 == 83
replace educ_loan = educ_loan + X2740 if X2727 == 83
replace educ_loan = educ_loan + X2823 if X2810 == 83
replace educ_loan = educ_loan + X2840 if X2827 == 83
replace educ_loan = educ_loan + X2923 if X2910 == 83
replace educ_loan = educ_loan + X2940 if X2927 == 83

// other installment loadns
gen oth_install =  X7183 + X1044 + X1215 + X1219

// add consumer loans if purpose is other
replace oth_install = oth_install + X2723 if X2710 == 78
replace oth_install = oth_install + X2740 if X2727 == 78
replace oth_install = oth_install + X2823 if X2810 == 78
replace oth_install = oth_install + X2840 if X2827 == 78
replace oth_install = oth_install + X2923 if X2910 == 78
replace oth_install = oth_install + X2940 if X2927 == 78

// add to overall variable "installment loans"
gen inst_loan = veh_loan + educ_loan + oth_install 
}


if `num' == 92 {
// vechicle loans: personally owned cars and other owned vehicles
gen veh_loan  = X2218 + X2318 + X2418  + X2424 + X2519 + X2619 + X2625

// education loans: first five, and then all remaining together 
gen educ_loan = X7824 + X7847 + X7870 + X7924 + X7947 + X7970 

// add consumer loans into education loans if purpose was education
replace educ_loan = educ_loan + X2723 if X2710 == 83
replace educ_loan = educ_loan + X2740 if X2727 == 83
replace educ_loan = educ_loan + X2823 if X2810 == 83
replace educ_loan = educ_loan + X2840 if X2827 == 83
replace educ_loan = educ_loan + X2923 if X2910 == 83
replace educ_loan = educ_loan + X2940 if X2927 == 83

// other installment loans
gen oth_install =  X1044 + X1215 + X1219

// add consumer loans if purpose is other
replace oth_install = oth_install + X2723 if X2710 == 78
replace oth_install = oth_install + X2740 if X2727 == 78
replace oth_install = oth_install + X2823 if X2810 == 78
replace oth_install = oth_install + X2840 if X2827 == 78
replace oth_install = oth_install + X2923 if X2910 == 78
replace oth_install = oth_install + X2940 if X2927 == 78

// add to overall variable "installment loans"
gen inst_loan = veh_loan + educ_loan + oth_install 
}

if `num' < 92 {
// vechicle loans: personally owned cars and other owned vehicles
gen veh_loan  = X2218 + X2318 + X2418  + X2424 + X2519 + X2619 + X2625

// education loans indivdually: not recorded before 1992
gen educ_loan = 0

// add consumer loans into education loans if purpose was education
replace educ_loan = educ_loan + X2723 if X2710 == 83
replace educ_loan = educ_loan + X2740 if X2727 == 83
replace educ_loan = educ_loan + X2823 if X2810 == 83
replace educ_loan = educ_loan + X2840 if X2827 == 83
replace educ_loan = educ_loan + X2923 if X2910 == 83
replace educ_loan = educ_loan + X2940 if X2927 == 83

// other installment loans
gen oth_install =  X1044 + X1215 + X1219

// add consumer loans if purpose is other
replace oth_install = oth_install + X2723 if X2710 == 78
replace oth_install = oth_install + X2740 if X2727 == 78
replace oth_install = oth_install + X2823 if X2810 == 78
replace oth_install = oth_install + X2840 if X2827 == 78
replace oth_install = oth_install + X2923 if X2910 == 78
replace oth_install = oth_install + X2940 if X2927 == 78

// add to overall variable "installment loans"
gen inst_loan = veh_loan + educ_loan + oth_install 
}

// debt: other lines of credit ( lines of credit that are not secured by equity of home) 
gen othloc = 0 
replace othloc = othloc + X1108 if X1103 != 1
replace othloc = othloc + X1119 if X1114 != 1
replace othloc = othloc + X1130 if X1125 != 1

*fourth line of credit: assume same fraction not secured by home equity as for first three (above)
replace othloc = max(0,X1136)*(othloc/(X1108+X1119+X1130)) if othloc > 0 
*fourth line of credit: if not information on firt three credti lines, assume 50% secured by home equity
replace othloc = max(0,X1136)*0.5   if othloc == 0


// debt: other debt
gen odebt = 0

if `num' != 95 {
replace odebt = odebt + max(0,X4229) if X4230==5 // if not recorded earlier interview
replace odebt = odebt + max(0,X4329) if X4330==5
replace odebt = odebt + max(0,X4429) if X4430==5
replace odebt = odebt + max(0,X4829) if X4830==5
replace odebt = odebt + max(0,X4929) if X4930==5
replace odebt = odebt + max(0,X5029) if X5030==5

// add: borrowed on life insurance, miscellanous debts, margin loans
replace odebt = odebt + max(0,X4010) + max(0,X4032) + max(0,X3932)
}


if `num' == 95 {
replace odebt = odebt + max(0,X4229) if X4230==5
replace odebt = odebt + max(0,X4329) if X4330==5
replace odebt = odebt + max(0,X4429) if X4430==5
replace odebt = odebt + max(0,X4829) if X4830==5
replace odebt = odebt + max(0,X4929) if X4930==5
replace odebt = odebt + max(0,X5029) if X5030==5

// add: borrowed on life insurance, miscellanous debts, margin loans (latter coded differently)
replace odebt = odebt + max(0,X4010) + max(0,X4032)
replace odebt = odebt + max(0,X3932) if X7194==5
}

///// FINANCIAL ASSETS /////

// checking accounts
foreach var of varlist X3506 X3510 X3514 X3518 X3522 X3526 X3529 {
replace `var' = 0 if `var' < 0 
}
gen checking = X3506 + X3510 + X3514 + X3518 + X3522 + X3526 + X3529

// prepaid debit card or prepaid government benefit card (not availabe before 2016)
gen prepaid = . 

// IRA/KEOGH accounts (roth IRAs, roll-over IRAs, regular IRAs, Keogh)
foreach var of varlist X3610 X3620 X3630 {
replace `var' = 0 if `var' < 0 
}
gen IRAKH     = X3610 + X3620 + X3630

// account referring to reference person
gen IRAKH_ref = X3610
// account referring to spouse
gen IRAKH_sp  = X3620
// account referring to other familiy members 
gen IRAKH_oth = X3630

// how is the money in accounts invested (all in stocks, all in bonds etc.)
ren X3631 IRAKH_invest

// certificates of deposits (cds)
ren X3721 cds

// savings + money market accounts (recorded separately)
foreach var of varlist X3706 X3706 X3716 X3718 ///
			X3804 X3807 X3810 X3813 X3816 X3818 {
replace `var' = 0 if `var' < 0 
}
* money market accounts
gen mm      = X3706 + X3706 + X3716 + X3718
* savings accounts
gen savings = X3804 + X3807 + X3810 + X3813 + X3816 + X3818

// mutual funds
if `num' > 92 {
ren X6704 totmutf  	// market value total mutual funds or hedge funds
}
ren X3822 stmutf 	// market value of all stock mutual funds and ETFs
ren X3824 tfbmutf	// market value of tax free bond mutual funds and ETFs
ren X3826 gbmutf	// market value of US gov backed bond mutual funds and ETFs
ren X3828 obmutf	// market value of other mutual funds and ETFs
ren X3830 comutf	// market value of combination funds and ETFs

// bonds
if `num' >= 92 {
ren X3902 savbnd	// total value of all savings bonds
ren X7635 mortbnd	// total value of mortgage backed bonds
ren X7637 notxbnd	// total value of tax free bonds (such as municipal bonds)
ren X7636 govtbnd	// US Government bonds or treasury bills

// total value of other bonds (corporate bonds and foreign bonds)
replace X7639 = 0 if X7639 < 0
replace X7638 = 0 if X7638 < 0
gen obnd = X7639 + X7638

ren X3906 mortbndf	// face value of mortgage backed bonds
ren X3908 govtbndf	// face value of governemnt backed bonds 
ren X3910 notxbndf  // face value of non-taxable bonds 

replace X7634 = 0 if X7634 < 0
replace X7632 = 0 if X7632 < 0
gen obndf = X7634 + X7632	// face value of corporate and foreign bonds
}

if `num' < 92 {
ren X3902 savbnd		// total value of all savings bonds
ren X3906 mortbndf		// face value of mortgage backed bonds
ren X3908 govtbndf		// face value of governemnt backed bonds
ren X3910 notxbndf		// face value of non-taxable bonds 
ren X3912 obndf 		// face value of corporate and foreign bonds  
} 

// stocks (total market value)
ren X3915 stocks

// dollar value of cash or call money accounts
replace X3930 = 0 if 3930 == -1 
ren X3930 call 

if `num' == 98 {
ren X6820 annuit  	// total dollar amount of annuities 
ren X6835 trusts	// total dollar amount of trusts/ managed investment accounts 
ren X6817 annuit_pay  // how much income from annuities?
ren X6832 trusts_pay  // how much income from trusts/managed investment account
ren X6841 trusts_invest  // how is trust invested? (all in stocks, all in bonds etc.
ren X6826 annuit_invest  // how is annuity invested? (all in stocks, all in bonds etc.
}

if `num' < 98 {
replace X3939 = 0 if X3939 == -1 
ren X3942 ann_trust			// total dollar value of interst in annuities, trusts, managed investment accounts
ren X3939 ann_trust_pay     // how much would you receive if cashed in?
ren X3947 ann_trust_invest  // how is trust/ annuity invested? (all in stocks, all in bonds etc.)
}

// face value of all life insurance policies
ren X4003 li

// current cash value of these policies
replace X4006 = 0 if X4006 <0
ren X4006 cashli

// all together, how much are you owed?
ren X4018 owed 

// other assets
foreach var of varlist X4022 X4026 X4030 {
replace `var' = 0 if `var' < 0
}
gen othasset =  X4022 + X4026 + X4030 + owed

// what is the current value of the trust or foundation (not recorded)
gen found = .

// future pensions: how much is in account now? (cumulated)
foreach var of varlist X5604 X5612 X5620 X5628 X5636 X5644 {
replace `var' = 0 if `var' < 0
}
gen futpen  = X5604+X5612+X5620+X5628+X5636+X5644

// current job pensions (including thrifts): balance on accounts (cumulated)
foreach var of varlist X4226 X4326 X4426 X4826 X4926 X5026 X4436 X5036 {
replace `var' = 0 if `var' < 0
}
gen thrift = X4226 + X4326 + X4426 + X4826 + X4926 + X5026 + X4436 + X5036

// current job pensions (including thrifts): balance on individual accounts
ren X4226 thrift1
ren X4326 thrift2
ren X4426 thrift3
ren X4826 thrift4
ren X4926 thrift5
ren X5026 thrift6
ren X4436 thrift7
ren X5036 thrift8

// how are current job pensions invested (all in stocks, all in bonds, etc.)
ren X4234 thrift_invest1
ren X4334 thrift_invest2
ren X4434 thrift_invest3
ren X4834 thrift_invest4
ren X4934 thrift_invest5
ren X5034 thrift_invest6

// further data cleaning

drop X* J*

replace sexsp   = . if sexsp   == 0
replace agesp   = . if agesp   == 0
replace martsp1 = . if martsp1 == 0
replace educsp  = . if educsp  == 0
replace martsp  = . if martsp  == 0
replace bysp 	= . if bysp == 0

// recoding of education before 2016
foreach x in educref educsp {
replace `x' = . if `x' == -1		// code "less than first grade" as missing
foreach s of numlist 2/4 {
replace `x' = 1 if `x' == `s'		//  first to fourth grade combined
} 
foreach s of numlist 5/6 {			//  5th or 6th grade combined
replace `x' = 2 if `x' == `s' 
}
foreach s of numlist 7/8 {			//  7th or 8th grade combined
replace `x' = 3 if `x' == `s' 
}
replace `x' = 4 if `x' == 9 		//  recode: 9th grade
replace `x' = 5 if `x' == 10 		//  recode: 10th grade
replace `x' = 6 if `x' == 11 		//  recode: 11th grade
replace `x' = 7 if `x' == 12		//  recode: 12th grade
foreach s of numlist 13/17 {
replace `x' = 8 if `x' == `s'		// some college or above 
}
}


if `num' > 95 {
replace trusts_pay = 0 if trusts_pay < 0
replace annuit_pay = 0 if annuit_pay < 0 
} 
if `num' <= 95 {
replace ann_trust_pay = 0 if ann_trust_pay < 0
}

replace checking = 0 if checking < 0
replace prepaid  = 0 if prepaid < 0 
replace call 	 = 0 if call == -1
replace cashli 	 = 0 if cashli == -1
replace found 	 = 0 if found == -1


global loop IRAKH_ref IRAKH_sp IRAKH_oth ///
trusts annuit savings mm futpen* thrift thrift1 thrift2 ///
thrift3 thrift4 thrift5 thrift6 thrift7 thrift8

foreach var in $loop {
    capture confirm variable `var'_ps, exact
    if !_rc {
        display "`var'_ps exists"
		replace `var'_ps = 0 if `var'_ps == -1
		replace `var'_ps = `var'_ps/10000 
    }
    else {
        display "`var'_ps does not exist"
    }
}

gen year = 19`num' 

save "$scfwkddata/19`num'_adj.dta", replace 
}

/////////////////////////////////////////////////////////////////
////////////////////// combine all waves ////////////////////////
/////////////////////////////////////////////////////////////////

use "$scfwkddata/2016_adj.dta", clear

foreach num of numlist 2013(-3)1989 {
append using "$scfwkddata/`num'_adj.dta"
}
order peu_num YY1 Y1 
save "$scfwkddata/all_adj.dta", replace



